{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "",
   "id": "5cc12e52621f9633"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# 1、基于LCEL语法的新的chain:create_sql_query_chain\n",
    "\n",
    "SQLDatabase的使用"
   ],
   "id": "44280c9a66e1092a"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-05T12:05:52.689086Z",
     "start_time": "2025-08-05T12:05:52.573694Z"
    }
   },
   "cell_type": "code",
   "source": [
    "from langchain.chains.sql_database.query import create_sql_query_chain\n",
    "from langchain_community.utilities.sql_database import SQLDatabase\n",
    "\n",
    "#访问mysql数据库\n",
    "## mysql+pymysql://用户名:密码@ip地址:端口号/数据库名\n",
    "db_name = \"root\"\n",
    "db_password = \"Password01!\"\n",
    "db_host = \"127.0.0.1\"\n",
    "db_port = 3306\n",
    "db_database = \"gmall\"\n",
    "db = SQLDatabase.from_uri(f\"mysql+pymysql://{db_name}:{db_password}@{db_host}:{db_port}/{db_database}\")\n",
    "\n",
    "#查询对应的数据库中包含的表\n",
    "# print(db.get_usable_table_names())\n",
    "\n",
    "\n",
    "# db.run(\"查新employees表中有多少条记录？\")  #错误的\n",
    "db.run(\"SELECT COUNT(*) FROM order_info\")"
   ],
   "id": "969a6aec6bdc042",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[(756,)]'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 4
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-05T12:06:28.522864Z",
     "start_time": "2025-08-05T12:06:24.555937Z"
    }
   },
   "cell_type": "code",
   "source": [
    "from langchain_openai import ChatOpenAI\n",
    "import os\n",
    "import dotenv\n",
    "\n",
    "dotenv.load_dotenv()\n",
    "\n",
    "os.environ[\"OPENAI_API_KEY\"] = os.getenv(\"OPENAI_API_KEY\")\n",
    "os.environ[\"OPENAI_BASE_URL\"] = os.getenv(\"OPENAI_BASE_URL\")\n",
    "\n",
    "# 1、获取大模型\n",
    "chat_model = ChatOpenAI(\n",
    "    model=\"gpt-4o-mini\"\n",
    ")\n",
    "\n",
    "\n",
    "chain = create_sql_query_chain(\n",
    "    llm = chat_model,\n",
    "    db = db,\n",
    ")\n",
    "\n",
    "# chain.invoke({\"question\":\"查询employees表中有多少条记录？\"})\n",
    "chain.invoke({\"question\":\"查询order_info表中最高薪资的员工和其最高薪资\"})"
   ],
   "id": "693fcff63ca51437",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SQLQuery: \\n```sql\\nSELECT `user_id`, MAX(`final_amount`) AS `最高薪资` \\nFROM `order_info` \\nGROUP BY `user_id` \\nORDER BY `最高薪资` DESC \\nLIMIT 1;\\n```'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# 2、LCEL语法之create_stuff_documents_chain （了解）\n",
    "\n"
   ],
   "id": "13a227cff0919908"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-04T03:46:06.744244Z",
     "start_time": "2025-08-04T03:46:05.097158Z"
    }
   },
   "cell_type": "code",
   "source": [
    "from langchain.chains.combine_documents import create_stuff_documents_chain\n",
    "from langchain_core.prompts import PromptTemplate\n",
    "from langchain_openai import ChatOpenAI\n",
    "from langchain_core.documents import Document\n",
    "\n",
    "# 定义提示词模板\n",
    "# prompt = PromptTemplate.from_template(\"\"\"\n",
    "# 如下文档{docs}中说，香蕉是什么颜色的？\n",
    "# \"\"\")\n",
    "\n",
    "prompt = PromptTemplate.from_template(\"\"\"\n",
    "如下文档{docs}的内容，回复{question}？\n",
    "\"\"\")\n",
    "\n",
    "# 创建链\n",
    "llm = ChatOpenAI(model=\"gpt-4o-mini\")\n",
    "chain = create_stuff_documents_chain(llm, prompt, document_variable_name=\"docs\")\n",
    "\n",
    "# 文档输入\n",
    "docs = [\n",
    "    Document(\n",
    "        page_content=\"苹果，学名Malus pumila Mill.，别称西洋苹果、柰，属于蔷薇科苹果属的植物。苹果是全球最广泛种植和销售的水果之一，具有悠久的栽培历史和广泛的分布范围。苹果的原始种群主要起源于中亚的天山山脉附近，尤其是现代哈萨克斯坦的阿拉木图地区，提供了所有现代苹果品种的基因库。苹果通过早期的贸易路线，如丝绸之路，从中亚向外扩散到全球各地。\"\n",
    "    ),\n",
    "    Document(\n",
    "        page_content=\"香蕉是白色的水果，主要产自热带地区。\"\n",
    "\n",
    "    ),\n",
    "    Document(\n",
    "        page_content=\"蓝莓是蓝色的浆果，含有抗氧化物质。\"\n",
    "\n",
    "    ),\n",
    "    Document(\n",
    "        page_content=\"我的名字叫小明。\"\n",
    "\n",
    "    )\n",
    "]\n",
    "# 执行摘要\n",
    "chain.invoke({\"docs\": docs,\"question\":\"我的名字叫什么？\"})"
   ],
   "id": "c0b42a55a73bdce8",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'你的名字叫小明。'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 14
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
